import sqlite3
import akshare as ak
from datetime import datetime
import util
import pandas as pd

conn, cursor = util.getDb()


if __name__ == "__main__":

    # 查询 market_daily 表中已存在的日期
    cursor.execute("SELECT date FROM market_daily")
    existing_dates = {row[0] for row in cursor.fetchall()}

    # 获取当前市场资金流数据
    stock_market_fund_flow_df = ak.stock_market_fund_flow()

    # 遍历数据并插入缺失的记录
    for _, row in stock_market_fund_flow_df.iterrows():
        if row["日期"] not in existing_dates:
            cursor.execute(
                """
                INSERT INTO market_daily (
                    sh_close, sh_rate, sz_close, sz_rate, date, 
                    main_net_inflow, main_net_ratio, 
                    huge_net_inflow, huge_net_ratio,
                    large_net_inflow, large_net_ratio, 
                    medium_net_inflow, medium_net_ratio,
                    small_net_inflow, small_net_ratio
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
                (
                    row["上证-收盘价"],
                    row["上证-涨跌幅"],
                    row["深证-收盘价"],
                    row["深证-涨跌幅"],
                    row["日期"],  # 日期
                    row["主力净流入-净额"],
                    row["主力净流入-净占比"],
                    row["超大单净流入-净额"],
                    row["超大单净流入-净占比"],
                    row["大单净流入-净额"],
                    row["大单净流入-净占比"],
                    row["中单净流入-净额"],
                    row["中单净流入-净占比"],
                    row["小单净流入-净额"],
                    row["小单净流入-净占比"],
                ),
            )

    # 提交更改并关闭连接
    conn.commit()
    conn.close()
